
package us.gaaoc.framework.dao;


import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;

import us.gaaoc.framework.businessmodel.Filing;
import us.gaaoc.framework.model.CaseRecord;
import us.gaaoc.framework.model.CaseRecordHistory;

/**
 * Home object for domain model class CaseRecordHistory.
 * 
 * @see us.gaaoc.framework.model.CaseRecordHistory
 * @author Hibernate Tools
 */

/**
 * Home object for domain model class CaseRecordHistory.
 * 
 * @see us.gaaoc.framework.model.CaseRecordHistory
 * @author Hibernate Tools
 */
public class CaseRecordHistoryDAOHibernate extends GenericHibernateDAO<CaseRecordHistory, String, CaseRecordHistoryDAO>
implements  CaseRecordHistoryDAO{

	protected CaseRecordHistoryDAOHibernate() {}

	@SuppressWarnings("unchecked")
	public List<CaseRecordHistory> findHistoryByCaseRecord(CaseRecord caseRecord) {
		Query q = this.getSession().createQuery("from CaseRecordHistory crh where crh.caseRecord = ? ORDER BY crh.eventDateTime DESC");
		q.setEntity(0, caseRecord);
		return q.list(); 
	}
	
	public Date findAttorneySubmissionDate(String caseRecordUuid) {
		/*String sql = "SELECT  h.eventDateTime from  (SELECT r.eventID , t.description, r.eventDateTime, r.caseRecordUuid " +
				" FROM caseRecordHistory r,  caseRecordEventType t	 where r.eventTypeCode = t.code and r.eventID in " +
				" (select max(z.eventID) from caseRecordHistory z group by   z.caseRecordUuid) group by r.caseRecordUuid) as h" +
				" where caseRecordUuid=?";*/
		// Modified query to pull only dates for "Received" events
		// After errors in importing, sometimes the latest event is not always
		// the received date and time, so updated query to only pull the latest
		// event where the event type code is 1 for "Filing Received" events
		String sql = "SELECT  MAX(crh.eventDateTime) "
			+ "FROM caseRecordHistory crh, caseRecordEventType cret "
			+ "WHERE crh.caseRecordUuid= ? "
			+ "AND crh.eventTypeCode=1 "
			+ "AND crh.eventTypeCode=cret.code "
			+ "GROUP BY crh.caseRecordUuid";
		Query q = getSession().createSQLQuery(sql);
		q.setString(0, caseRecordUuid);
		Object results = q.uniqueResult();
		Date eventDate = (Date) results;
		return eventDate;
	
	}
	
	public CaseRecordHistory findLatestEventByCaseRecord(CaseRecord caseRecord) {
		String sql = "select new CaseRecordHistory(h.caseRecord, h.eventType, max(h.eventTime)) FROM CaseRecordHistory h where h.caseRecord = ? group by h.caseRecord ";
		Query q = this.getSession().createQuery(sql);
		q.setEntity(0, caseRecord);
		return (CaseRecordHistory)q.uniqueResult();	
	}

 	public List<Filing>  findCaseFilingswithErrorEvent() {
 		List<Filing> filings = new ArrayList<Filing>();
		String sql = "select h.caseRecord, max(h.eventDateTime), h.eventType.description from CaseRecordHistory h join h.caseRecord c where c.clearFilingFlag = false and c.deletedFilingFlag = false and c.archivedFlag = false and h.eventType.code = 7 group by h.caseRecord ";
		Query q = this.getSession().createQuery(sql);
		List results =  q.list();
		Iterator i = results.iterator();
		while (i.hasNext()) {
			Object[] result = (Object[]) i.next();
			CaseRecord cr =  (CaseRecord) result[0];
			String uuid = cr.getUuid();
			String caseTrackingID = cr.getCaseTrackingId();
			String status = cr.getStatus().getDescription();
			Date instantiationDate = cr.getInstantiationDateTime();
			Filing f = new Filing(uuid, caseTrackingID, status, instantiationDate);
			f.setEventDateTime((Date)result[1]);
			f.setEventType((String)result[2]);
			filings.add(f);
		}
		return filings;
		
	} 
	
	
	
	

	 
}
